package com.iotek.bookstore.modle.dao.impl;

import com.iotek.bookstore.modle.entity.Book;
import com.iotek.bookstore.util.PreparedStatementSetter;
import com.iotek.bookstore.util.RsCallBackHandler;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class BookDaoImpl extends MyBookstoreDaoImpl<Book>{

    /*
    * 添加图书简易信息
    * */
    @Override
    public int insert(final Book book) {
        String sql="insert into book(bookId,titleTwoId,cover,book,writer,press,publicationTime,intro,pricing,price,repertory) " +
            "values(?,?,?,?,?,?,?,?,?,?,?)";
        return this.update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement pstmt) throws SQLException {
                pstmt.setInt(1,book.getBookId());
                pstmt.setInt(2,book.getTitleTwoId());
                pstmt.setString(3,book.getCover());
                pstmt.setString(4,book.getBook());
                pstmt.setString(5,book.getWriter());
                pstmt.setString(6,book.getPress());
                pstmt.setString(7,book.getPublicationTime());
                pstmt.setString(8,book.getIntro());
                pstmt.setDouble(9,book.getPricing());
                pstmt.setDouble(10,book.getPrice());
                pstmt.setInt(11,book.getRepertory());
            }
        });
    }

    /*
    * 根据图书ID删除图书信息
    * */
    @Override
    public int delete(final int id) {
        String sql="delete from book where bookId=?";
        return this.update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement pstmt) throws SQLException {
                pstmt.setInt(1,id);
            }
        });
    }

    /*
    * 根据图书ID更新图书信息
    * */
    @Override
    public int update(final Book book) {
        if(book.getBook()==null){
            //更新图书销量和库存
            String sql="update  book set sales=?,repertory=? where bookId=? ";
            return this.update(sql, new PreparedStatementSetter() {
                public void setValues(PreparedStatement pstmt) throws SQLException {
                    pstmt.setInt(1,book.getSales());
                    pstmt.setInt(2,book.getRepertory());
                    pstmt.setInt(3,book.getBookId());
                }
            });
        }else {
            //更新图书信息
            String sql="update  book set titleTwoId=?,book=?,writer=?,press=?,publicationTime=?,intro=?,pricing=?,price=?,repertory=? where bookId=? ";
            return this.update(sql, new PreparedStatementSetter() {
                public void setValues(PreparedStatement pstmt) throws SQLException {
                    pstmt.setInt(1,book.getTitleTwoId());
                    pstmt.setString(2,book.getBook());
                    pstmt.setString(3,book.getWriter());
                    pstmt.setString(4,book.getPress());
                    pstmt.setString(5,book.getPublicationTime());
                    pstmt.setString(6,book.getIntro());
                    pstmt.setDouble(7,book.getPricing());
                    pstmt.setDouble(8,book.getPrice());
                    pstmt.setInt(9,book.getRepertory());
                    pstmt.setInt(10,book.getBookId());
                }
            });
        }

    }

    /*
    * 查询所有的书籍
    * */
    @Override
    public List<Book> queryAll() {
        String sql="SELECT bookId,cover,book,writer,press,publicationTime,intro,pricing,price,repertory FROM book ";

        return commonQuery(sql);
    }


    @Override
    public List<Book> query(String str) {
        if(str.equals("time")){
            list=byTimeQuery();
        }else if(str.equals("sales")){
            list=bySales();
        }
        return list;
    }

    //根据上架时间降序查询所有的书籍
    public List<Book>byTimeQuery(){
        String sql="SELECT bookId,cover,book,writer,press,publicationTime,intro,pricing,price,repertory FROM book\n" +
            "ORDER BY putawayTime DESC";

        return commonQuery(sql);
    }

    //根据销量降序查询所有的书籍
    public List<Book>bySales(){
        String sql="SELECT bookId,cover,book,writer,press,publicationTime,intro,pricing,price,repertory FROM book\n" +
            "ORDER BY sales DESC";

        return commonQuery(sql);
    }

    //根据二级分类查询书籍按销量降序排序
    @Override
    public List<Book> query(int id) {
        String sql="SELECT bookId,cover,book,writer,press,publicationTime,intro,pricing,price,repertory FROM book\n" +
            "where titleTwoId=?\n" +
            "ORDER BY sales DESC ";
        return commonQuery(sql,id);
    }

    //根据二级分类ID查找书籍按照书籍上架时间或销量排序或不排序
    @Override
    public List<Book> query(int id,int start,int end, String type) {
        if(type.equals("sales")){
            String sql="SELECT bookId,cover,book,writer,press,publicationTime,intro,pricing,price,repertory FROM book\n" +
                "where titleTwoId=?\n" +
                "\n" +
                "ORDER BY sales DESC  \n" +
                "LIMIT ?,?";
            list=commonQuery(sql,id,start,end);
        }else if(type.equals("time")){
            String sql="SELECT bookId,cover,book,writer,press,publicationTime,intro,pricing,price,repertory FROM book\n" +
                "where titleTwoId=?\n" +
                "\n" +
                "ORDER BY putawayTime DESC  \n" +
                "LIMIT ?,?";
            list=commonQuery(sql,id,start,end);
        }else if (type.equals("synthesize")){
            String sql="SELECT bookId,cover,book,writer,press,publicationTime,intro,pricing,price,repertory FROM book\n" +
                "where titleTwoId=?\n" +
                "\n" +
                "ORDER BY putawayTime DESC  \n" +
                "LIMIT ?,?";
            list=commonQuery(sql,id,start,end);
        }

        return list;
    }

    //不含占位符查询书籍的通用方法
    private List<Book> commonQuery(String sql){
        this.query(sql, null, new RsCallBackHandler() {
            public void processRs(ResultSet rs) throws SQLException {
                while (rs.next()){
                    list.add(createBook(rs));
                }
            }
        });
        return list;
    }

    //根据ID查询书籍的通用方法
    private List<Book> commonQuery(String sql,final int id){
        this.query(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement pstmt) throws SQLException {
                pstmt.setInt(1,id);
            }
        }, new RsCallBackHandler() {
            public void processRs(ResultSet rs) throws SQLException {
                while (rs.next()) {
                    list.add(createBook(rs));
                }
            }
        });
        return list;
    }

    //根据图书二级ID查找一定指定条数的数据并根据条件排序的通用方法
    private List<Book> commonQuery(String sql,final int id, final int start, final int end){
        this.query(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement pstmt) throws SQLException {
                pstmt.setInt(1, id);
                pstmt.setInt(2, start);
                pstmt.setInt(3, end);
            }
        }, new RsCallBackHandler() {
            public void processRs(ResultSet rs) throws SQLException {
                while (rs.next()){
                    list.add(createBook(rs));
                }
            }
        });
        return list;
    }

    //创建Book的实例
    private Book createBook(ResultSet rs)throws SQLException{
        Book book=new Book();
        book.setBookId(rs.getInt(1));
        book.setCover(rs.getString(2));
        book.setBook(rs.getString(3));
        book.setWriter(rs.getString(4));
        book.setPress(rs.getString(5));
        book.setPublicationTime(rs.getString(6));
        book.setIntro(rs.getString(7));
        book.setPricing(rs.getDouble(8));
        book.setPrice(rs.getDouble(9));
        book.setRepertory(rs.getInt(10));
        return book;
    }
}
